﻿-- =============================================
-- Script Template
-- =============================================
SELECT
         CONVERT(varchar(20), CONVERT(datetime, d.TRANSDATE), 120) AS 'TRANSDATE' -- дата
       -- , d.INVENTLOCATIONID AS ShopID -- Код магазина в Аксапте
       , i.Name AS ShopName -- Наименование магазина в Аксапте
       -- , d.ITEMRANGEID AS DepartmentID -- Код секции
       , r.Name AS DepartmentName -- Наименование секции
       , SUM(d.AMOUNTSALE)  AS 'AMOUNTSALE' -- Сумма реализации
       , SUM(d.AMOUNTPURCH) AS 'AMOUNTPURCH' -- Сумма проданного товара в закупочных ценах
       , SUM(d.RETURNAMOUNTPURCH) AS 'RETURNAMOUNTPURCH' -- Сумма возвратов в закупочных ценах
       , SUM(d.RETURNAMOUNTSALE) AS RETURNAMOUNTSALE -- Сумма возвратов
       , SUM(d.OPTAMOUNTPURCH) AS OPTAMOUNTPURCH -- Сумма оптовой реализации в закупочных ценах
       , SUM(d.OPTAMOUNTSALE) AS OPTAMOUNTSALE -- Сумма оптовой реализации
FROM RETAILCASHREPORTDETAILS AS d
	INNER JOIN INVENTLOCATION AS i ON d.INVENTLOCATIONID = i.INVENTLOCATIONID
	INNER JOIN INVENTITEMRANGE AS r ON d.ITEMRANGEID = r.RANGEID
GROUP BY TRANSDATE, i.Name, r.Name
HAVING (TRANSDATE >= GETDATE() - 2)
ORDER BY TRANSDATE DESC, i.Name, r.Name

-----------------------
SELECT
	  CONVERT(varchar(20), CONVERT(datetime, d.TRANSDATE), 120) AS 'дата'
	, i.NAME AS 'магазин'
	, r.NAME AS 'секция'
	, SUM(d.AMOUNTSALE) AS 'Сумма реализации'
	, SUM(d.AMOUNTPURCH) AS 'Сумма проданного товара в закупочных ценах'
	, SUM(d.RETURNAMOUNTPURCH) AS 'Сумма возвратов в закупочных ценах'
	, SUM(d.RETURNAMOUNTSALE) AS 'Сумма возвратов'
	, SUM(d.OPTAMOUNTPURCH) AS 'Сумма оптовой реализации в закупочных ценах'
	, SUM(d.OPTAMOUNTSALE) AS 'Сумма оптовой реализации'
FROM RETAILCASHREPORTDETAILS AS d
	INNER JOIN INVENTLOCATION AS i ON d.INVENTLOCATIONID = i.INVENTLOCATIONID
	INNER JOIN INVENTITEMRANGE AS r ON d.ITEMRANGEID = r.RANGEID
GROUP BY d.TRANSDATE, i.NAME, r.NAME
HAVING      (d.TRANSDATE >= GETDATE() - 2)
ORDER BY TRANSDATE DESC, i.Name, r.Name


